<?php

namespace helper;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Exception;

class Excel
{

  /**
   * 导出
   * @param array $column
   * @param array $data
   * @param string $file
   * @param string $writeType
   * @throws \PhpOffice\PhpSpreadsheet\Exception
   * @throws \Exception
   */
  static function export($column = [], $data = [], $file = '', $writeType = 'xlsx')
  {
    $fileName    = empty($file) ? date('YmdHis') : $file . '_' . date('YmdHis');
    $spreadsheet = new Spreadsheet();
    //横向单元格标识
    $cellName = array(
        'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R',
        'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH',
        'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW',
        'AX', 'AY', 'AZ'
    );
    $sheet    = $spreadsheet->getActiveSheet();
    $types    = [];
    $fields   = [];
    if ($column) {
      foreach ($column AS $k => $v) {   //设置列标题
        //处理列宽
        if (isset($v['width'])) {
          $sheet->getColumnDimension($cellName[$k])->setWidth($v['width']);
        } else {
          $sheet->getColumnDimension($cellName[$k])->setAutoSize(true);
        }
        $sheet->setCellValue("{$cellName[$k]}1", $v['title']);
        $types[$k] = empty($v['type']) ? 's' : $v['type']; //s,f,n,b
      }
      $fields = array_column($column, 'field');
    }
    //填写数据
    if ($data) {
      $i = 2;
      foreach ($data AS $_v) {
        $j = 0;
        foreach ($_v AS $_key => $_cell) {
          if (!empty($column) && !in_array($_key, $fields)) continue;
          $sheet->setCellValueExplicit("{$cellName[$j]}{$i}", $_cell, "{$types[$j]}");
          $j++;
        }
        $i++;
      }
    }
    $ext      = strtolower($writeType);
    $objWrite = IOFactory::createWriter($spreadsheet, ucfirst($writeType));
    if ($ext == 'csv') $objWrite->setUseBOM(true);
    ob_end_clean();
    header('pragma:public');
    header("Content-Disposition:attachment;filename={$fileName}.{$ext}");
    $objWrite->save('php://output');
    $spreadsheet->disconnectWorksheets();
    unset($spreadsheet);
    exit;
  }

  /**
   * 导入文件
   * @param $file
   * @param int $startRow
   * @param null $inputCoding
   * @return array
   * @throws \PhpOffice\PhpSpreadsheet\Exception
   * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
   */
  static public function import($file, $startRow = 2, $inputCoding = null)
  {
    ini_set('max_execution_time', '0');
    if (empty($file) OR !file_exists($file)) exception('文件不存在');
    $inputFileType = IOFactory::identify($file); //传入文件路径
    $excelReader   = IOFactory::createReader($inputFileType);
    if ($inputFileType == 'Csv' && $inputCoding !== null) {
      $excelReader->setInputEncoding($inputCoding); //GBK乱码解决
    }
    $excelReader->setReadDataOnly(true); // 如果不需要获取特殊操作，则只读内容，可以大幅度提升读取Excel效率
    $PHPExcel = $excelReader->load($file); // 载入excel文件
    $sheet    = $PHPExcel->getSheet(0); // 读取第一個工作表
    //获取总列数
    $allColumn = $sheet->getHighestColumn();
    //获取总行数
    $allRow = $sheet->getHighestRow();
    ++$allColumn;
    //循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
    $data = [];
    for ($currentRow = $startRow; $currentRow <= $allRow; $currentRow++) {
      //从哪列开始，A表示第一列
      for ($currentColumn = 'A'; $currentColumn != $allColumn; $currentColumn++) {
        //数据坐标
        $address = $currentColumn . $currentRow;
        //读取到的数据，保存到数组$arr中
        $value               = trim($sheet->getCell($address)->getValue());
        $data[$currentRow][] = $value;
      }
    }
    return $data;
  }
}